﻿using System;
using System.Data;
using System.Data.Common;
using Connector;
using System.ComponentModel;
using System.Collections.Generic;

namespace DBSearch
{
    /// <summary>
    /// This tool is written by Lukas Oestemer. There is no warrenty. All rights reserved.
    /// For bugs and issues contact Lukas.Oestemer@avande.com
    /// 
    /// Features:
    ///     - Connects to a database via .NET DB Provider
    ///     - Gets all data types providing from the Database server (if provider supports it)
    ///     - Searches a value in all columns of a data base type or mapped .NET type (if provider supports it)
    ///     - Logs errors into a log file.
    ///     - Supports multythreadding via background worker for search process.
    /// </summary>
    public class Connector
    {
        /// <summary>
        /// Prefix for SQL query parameters. <br/>
        /// <b>MSSQL:</b> @ <br/>
        /// <b>MySQL:</b> ? <br/>
        /// <b>SQLCE:</b> ? <br/>
        /// </summary>
        public String ParameterPrefix { get; private set; }

        /// <summary>
        /// Connection configuration string. E.g.: <br/> 
        /// MSSQL:  Data Source=SERVER\INSTANCE_NAME;Initial Catalog=DBNAME;Integrated Security=True;<br/>
        /// MySQL:  Server=SERVER_NAME_OR_IP;UserId=USER_NAME;Password=PW;Database=DB_NAME"<br/>
        /// SQLCE:  Data Source=..\DBFile.sdf
        /// </summary>
        public String ConnectionString { get; private set; }

        /// <summary>
        /// Full qualified name of the installed DB provider. E.g.:<br/>
        /// MSSQL: "System.Data.SqlClient"<br/>
        /// MySQL: "MySql.Data.MySqlClient"<br/>
        /// SQLCE: "System.Data.SqlServerCe.3.5"
        /// </summary>
        public DbProviderFactory DbProvider { get; private set; }

        /// <summary>
        /// current connection instance.
        /// </summary>
        private DbConnection _dbConnection;

        /// <summary>
        /// storing all types from the GetDataTypes method.
        /// </summary>
        private DataTable _dbTypes;

        /// <summary>
        /// Singelton DbConnection instance.
        /// </summary>
        public DbConnection Connection
        {
            get
            {
                if (_dbConnection == null)
                {
                    _dbConnection = DbProvider.CreateConnection();
                    _dbConnection.ConnectionString = ConnectionString;
                }
                if (_dbConnection.State == ConnectionState.Closed)
                    _dbConnection.ConnectionString = ConnectionString;
                return _dbConnection;
            }
        }

        /// <summary>
        /// Connects to any database providing a .NET DB provider.
        /// </summary>
        /// <param name="dbProvider"><see cref="DbProvider"/></param>
        /// <param name="connectionString"><see cref="ConnectionString"/></param>
        /// <param name="parameterPrefix"><see cref="ParameterPrefix"/></param>
        public Connector(string dbProvider, string connectionString, string parameterPrefix)
            : this(dbProvider, connectionString)
        {
            ParameterPrefix = parameterPrefix;
        }

        /// <summary>
        /// Connects to any database providing a .NET DB provider.
        /// </summary>
        /// <param name="dbProvider"><see cref="DbProvider"/></param>
        /// <param name="connectionString"><see cref="ConnectionString"/></param>
        public Connector(string dbProvider, string connectionString)
        {
            DbProvider = DbProviderFactories.GetFactory(dbProvider);
            ConnectionString = connectionString;
            Connection.ConnectionString = ConnectionString;
        }

        /// <summary>
        /// Retrives all data base data types from the server.
        /// </summary>
        /// <returns>DataTable containing all data type information from the SQL Server</returns>
        public DataTable GetDataTypes()
        {
            _dbTypes = new DataTable();
            using (Connection)
            {
                try
                {
                    Connection.Open();
                    _dbTypes = Connection.GetSchema("DataTypes");
                }
                catch (Exception ex)
                {
                    Logger.LogError(ex);
                }
                finally
                {
                    Connection.Close();
                }
            }
            return _dbTypes;
        }

        /// <summary>
        /// Searches a value of a given type in the databse as exact or like search.
        /// </summary>
        /// <param name="dbType">Type of the column the value has.</param>
        /// <param name="isDotNetType">True: the .NET Type mapping will be used for the search, otherwise false.</param>
        /// <param name="value">Value to look up.</param>
        /// <param name="likeSearch">True: The search will be performed with LIKE operator, otherwise false.</param>
        /// <param name="processChanged">informs about the process after every table (user object containing {totalTables,processedTables})</param>
        /// <param name="RunWorkerCompleted">Contains the result data set after work is done.</param>
        /// <returns>DataSet containing all tables with all found values.</returns>
        public void SearchValueAsync(string dbType, bool isDotNetType, object value, bool likeSearch, ProgressChangedEventHandler processChanged, RunWorkerCompletedEventHandler RunWorkerCompleted)
        {
            BackgroundWorker bw = new BackgroundWorker();
            bw.WorkerReportsProgress = true;

            bw.DoWork += DoWork;
            bw.ProgressChanged += processChanged;
            bw.RunWorkerCompleted += RunWorkerCompleted;

            bw.RunWorkerAsync(new object[] { dbType, isDotNetType, value, likeSearch });
        }

        /// <summary>
        /// Runs the search in a seperate thread.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DoWork(object sender, DoWorkEventArgs e)
        {
            if (!(e.Argument is object[]))
                return;

            var args = (object[])e.Argument;

            if (args.Length != 4 || !(args[0] is string) || !(args[1] is bool) || !(args[3] is bool))
                return;

            e.Result = SearchValue((string)args[0], (bool)args[1], args[2], (bool)args[3], (BackgroundWorker)sender);
        }

        /// <summary>
        /// Searches a value of a given type in the databse as exact or like search.
        /// </summary>
        /// <param name="dbType">Type of the column the value has.</param>
        /// <param name="isDotNetType">True: the .NET Type mapping will be used for the search, otherwise false.</param>
        /// <param name="value">Value to look up.</param>
        /// <param name="likeSearch">True: The search will be performed with LIKE operator, otherwise false.</param>
        /// <returns>DataSet containing all tables with all found values.</returns>
        public DataSet SearchValue(string dbType, bool isDotNetType, object value, bool likeSearch)
        {
            return SearchValue(dbType, isDotNetType, value, likeSearch, null);
        }

        /// <summary>
        /// Searches a value of a given type in the databse as exact or like search.
        /// </summary>
        /// <param name="dbType">Type of the column the value has.</param>
        /// <param name="isDotNetType">True: the .NET Type mapping will be used for the search, otherwise false.</param>
        /// <param name="value">Value to look up.</param>
        /// <param name="likeSearch">True: The search will be performed with LIKE operator, otherwise false.</param>
        /// <param name="asyncBw">optional background worker for asynch process reports.</param>
        /// <returns>DataSet containing all tables with all found values.</returns>
        private DataSet SearchValue(string dbType, bool isDotNetType, object value, bool likeSearch, BackgroundWorker asyncBw)
        {
            DbDataAdapter adapter = DbProvider.CreateDataAdapter();
            var resultDS = new DataSet();

            try
            {
                using (Connection)
                {
                    Connection.Open();
                    DataTable schema = Connection.GetSchema("Tables");
                    DataTable columns = Connection.GetSchema("Columns");

                    //
                    // for async process changed event
                    int tableCount = schema.Rows.Count != 0 ? schema.Rows.Count : 1;
                    int processedTables = 0;
                    double step = 100.0 / (double)tableCount;
                    string typeColumn = String.Empty;

                    List<string> reqDotNetMappedTypes = new List<string>();
                    if (isDotNetType)
                    {
                        if(_dbTypes == null)
                            return null;

                        foreach (DataRow row in _dbTypes.Rows)
                        {
                            if(String.Compare(row["DataType"].ToString(), dbType, true) == 0)
                                reqDotNetMappedTypes.Add(row["TypeName"].ToString());
                        }
                    }

                    foreach (DataRow table in schema.Rows)
                    {
                        if (String.Compare(table["Table_Type"].ToString(), "View", true) != 0)
                        {
                            string query = "SELECT * FROM " + table["Table_Name"] + " WHERE ";
                            foreach (DataRow column in columns.Select("Table_Name = '" + table["Table_Name"] + "'"))
                            {
                                if ((isDotNetType && reqDotNetMappedTypes.Contains(column["Data_Type"].ToString()))
                                    || (!isDotNetType && String.Compare(column["Data_Type"].ToString(), dbType, true) == 0))
                                {
                                    if (likeSearch)
                                        query += column["Column_Name"].ToString() + " LIKE '" + value + "' OR ";
                                    else
                                        query += column["Column_Name"].ToString() + " = '" + value + "' OR ";
                                }
                            }
                            if (String.Compare(query.Substring(query.Length - 4), " OR ") == 0)
                                query = query.Remove(query.Length - 4);

                            if (String.Compare(query.Substring(query.Length - 6), "WHERE ") != 0)
                            {
                                try
                                {
                                    adapter.SelectCommand = CreateCommand(query, false);
                                    adapter.Fill(resultDS, table["Table_Name"].ToString());
                                }
                                catch (Exception ex)
                                {
                                     Logger.LogError(ex);
                                }
                            }
                        }
                        processedTables++;
                        if (asyncBw != null)
                            asyncBw.ReportProgress((int)(step * processedTables), new object[] { tableCount, processedTables });
                    }
                }
            }
            catch (Exception ex)
            {
                 Logger.LogError(ex);
            }
            finally
            {
                Connection.Close();
            }

            for (int i = resultDS.Tables.Count - 1; i >= 0; i--)
            {
                if (resultDS.Tables[i].Rows.Count == 0)
                    resultDS.Tables.RemoveAt(i);
            }

            return resultDS;
        }

        /// <summary>
        /// Creates a new DbCommand.
        /// </summary>
        /// <param name="query">SQL query of the command.</param>
        /// <param name="insertPrefix">TRUE all parameter placeholders will replaced with the parameter prefix.
        /// Query has to contain {0} before every parameter.</param>
        /// <returns>The new command for excecution.</returns>
        private DbCommand CreateCommand(string query, bool insertPrefix)
        {
            DbCommand command = null;
            try
            {
                if (insertPrefix)
                    query = String.Format(query, ParameterPrefix);

                command = DbProvider.CreateCommand();
                command.Connection = Connection;
                command.CommandText = query;
            }
            catch (Exception ex)
            {
                 Logger.LogError(ex);
            }

            return command;
        }

        /// <summary>
        /// Creates a new DbParameter instance
        /// </summary>
        /// <param name="command">Command the parameter refers to.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="dbType">Type of the parameter</param>
        /// <param name="value">Value of the parameter</param>
        private void CreateParameter(DbCommand command, string parameterName, DbType dbType, object value)
        {
            try
            {
                DbParameter param = DbProvider.CreateParameter();
                param.ParameterName = parameterName;
                param.DbType = dbType;
                param.Value = value ?? DBNull.Value;

                command.Parameters.Add(param);
            }
            catch (Exception ex)
            {
                 Logger.LogError(ex);
            }
        }
    }
}
